|
RAIS
3.2
|
00001 00027 using System; 00028 using System.Collections.Generic; 00029 using System.Text; 00030 using RAIS.Common.DynamicMaskManagement; 00031 using System.Data; 00032 using System.Data.SqlClient; 00033 using RAIS.Common.TableManagement; 00034 00035 namespace RAIS.DataAccessLayer 00036 { 00037 public class DataRoleRestrictionManagement 00038 { 00039 #region PUBLIC METHODS 00040 00041 public static bool IsDataRoleRestricted(DynamicMaskData dynamicMaskData) 00042 { 00043 if (dynamicMaskData.CurrentUser.FacilitySpecified) 00044 return false; 00045 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00046 00047 SqlDataAdapter dAdapt = new SqlDataAdapter("SELECT * FROM [Data Role Additional Restriction] WHERE [FK Data Role ID] = " + dynamicMaskData.CurrentUser.DataRole.ID, con); 00048 DataTable dt = new DataTable(); 00049 dAdapt.Fill(dt); 00050 DataColumnCollection columns = GetAllDynamicMaskColumns(dynamicMaskData); 00051 00052 DataRow currentMaskDataRow = GetCategorySubcategoryAndMaskID(dynamicMaskData.DynamicMask.ID); 00053 foreach (DataRow row in dt.Rows) 00054 { 00055 string selectedField = row["Selected Field"].ToString(); 00056 if (IsDynamicMaskRestricted(row, currentMaskDataRow)) 00057 { 00058 if (columns.Contains(selectedField)) 00059 return true; 00060 else if (selectedField.StartsWith("PK ")) 00061 { 00062 string selectedFieldFK = "FK" + selectedField.Remove(0, 2); 00063 if (columns.Contains(selectedField) || columns.Contains(selectedFieldFK)) 00064 return true; 00065 } 00066 else if (selectedField.StartsWith("FK ")) 00067 { 00068 string selectedFieldPK = "PK" + selectedField.Remove(0, 2); 00069 if (columns.Contains(selectedField) || columns.Contains(selectedFieldPK)) 00070 return true; 00071 } 00072 } 00073 } 00074 return false; 00075 } 00076 00077 public static bool IsDataRoleRestricted(DynamicMaskData dynamicMaskData, DataRow restrictionRow) 00078 { 00079 if (dynamicMaskData.CurrentUser.FacilitySpecified) 00080 return false; 00081 DataColumnCollection maskColumns = GetAllDynamicMaskColumns(dynamicMaskData); 00082 DataRow currentMaskDataRow = GetCategorySubcategoryAndMaskID(dynamicMaskData.DynamicMask.ID); 00083 00084 if (dynamicMaskData.DynamicMask.MaskName.Contains("Device Manufacturing")) 00085 { 00086 DataRow dr = GetCategorySubcategoryAndMaskID(dynamicMaskData.DynamicMask.ID); 00087 string s = ""; 00088 } 00089 00090 string selectedField = restrictionRow["Selected Field"].ToString(); 00091 if (maskColumns.Contains(selectedField)) 00092 return true; 00093 else if (selectedField.StartsWith("PK ")) 00094 { 00095 string selectedFieldFK = "FK" + selectedField.Remove(0, 2); 00096 if (maskColumns.Contains(selectedField) || maskColumns.Contains(selectedFieldFK)) 00097 return true; 00098 } 00099 else if (selectedField.StartsWith("FK ")) 00100 { 00101 string selectedFieldPK = "PK" + selectedField.Remove(0, 2); 00102 if (maskColumns.Contains(selectedField) || maskColumns.Contains(selectedFieldPK)) 00103 return true; 00104 } 00105 return false; 00106 } 00107 00108 public static bool IsDataRoleRestricted(DataTable dt, RAIS.Common.UserManagement.UserData currentUser, int dynamicMaskID, ref string restrictions) 00109 { 00110 if (currentUser.FacilitySpecified) 00111 return false; 00112 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00113 SqlDataAdapter dAdapt = new SqlDataAdapter("SELECT * FROM [Data Role Additional Restriction] WHERE [FK Data Role ID] = " + currentUser.DataRole.ID, con); 00114 DataTable dtRestrictions = new DataTable(); 00115 dAdapt.Fill(dtRestrictions); 00116 00117 DataRow currentMaskDataRow = GetCategorySubcategoryAndMaskID(dynamicMaskID); 00118 foreach (DataRow row in dtRestrictions.Rows) 00119 { 00120 if (IsDynamicMaskRestricted(row, currentMaskDataRow)) 00121 { 00122 string selectedField = row["Selected Field"].ToString(); 00123 if (dt.Columns.Contains(selectedField)) 00124 restrictions += " AND [" + selectedField + "] IN (SELECT * FROM [" + row["Query Name"] + "]())"; 00125 else if (dt.Columns.Contains("FK" + selectedField.Remove(0, 2))) 00126 restrictions += " AND [FK" + selectedField.Remove(0, 2) + "] IN (SELECT * FROM [" + row["Query Name"] + "]())"; 00127 else if (dt.Columns.Contains("PK" + selectedField.Remove(0, 2))) 00128 restrictions += " AND [PK" + selectedField.Remove(0, 2) + "] IN (SELECT * FROM [" + row["Query Name"] + "]())"; 00129 } 00130 } 00131 00132 if (restrictions.Length > 0) 00133 return true; 00134 else return false; 00135 } 00136 00137 public static bool IsDataRoleRestricted(DataTable dt, int restrictionID) 00138 { 00139 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00140 SqlDataAdapter dAdapt = new SqlDataAdapter("SELECT * FROM [Data Role Additional Restriction] WHERE [PK Data Role Additional Restriction ID] = " + restrictionID, con); 00141 DataTable dtRestrictions = new DataTable(); 00142 dAdapt.Fill(dtRestrictions); 00143 00144 if (dtRestrictions.Rows.Count > 0) 00145 { 00146 DataRow row = dtRestrictions.Rows[0]; 00147 string selectedField = row["Selected Field"].ToString(); 00148 if (dt.Columns.Contains(selectedField) 00149 || (dt.Columns.Contains("FK" + selectedField.Remove(0, 2))) 00150 || (dt.Columns.Contains("PK" + selectedField.Remove(0, 2)))) 00151 return true; 00152 } 00153 00154 return false; 00155 } 00156 00157 public static void ApplyDataRoleRestrictions(DynamicMaskData dynamicMaskData) 00158 { 00159 string primaryKey = String.Empty; 00160 string colNames = String.Empty; 00161 string sqlString = GetSqlString(dynamicMaskData, ref primaryKey, ref colNames, true); 00162 sqlString += " WHERE tableAlias.[" + primaryKey + "] IS NOT NULL"; 00163 sqlString += GetRestrictions(dynamicMaskData); 00164 sqlString += " GROUP BY " + colNames; 00165 dynamicMaskData.DataAdapter = DynamicMaskManagement.GetDataAdapter(sqlString, "", dynamicMaskData.DataSet); 00166 } 00167 00168 public static void ApplyDataRoleRestrictions(DynamicMaskData dynamicMaskData, int key, string whereFieldName, int whereFieldValue) 00169 { 00170 Dictionary<int, string> fieldValues = new Dictionary<int, string>(); 00171 string sqlString = GetSqlString(dynamicMaskData, key, whereFieldName, whereFieldValue); 00172 using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString)) 00173 { 00174 SqlCommand sqlCommand = new SqlCommand(sqlString, con); 00175 sqlCommand.CommandType = System.Data.CommandType.Text; 00176 con.Open(); 00177 SqlDataReader sqlReader = sqlCommand.ExecuteReader(); 00178 while (sqlReader.Read()) 00179 { 00180 int keyValue; 00181 keyValue = (int)sqlReader[dynamicMaskData.DynamicMask.Table.PrimaryKeyFieldName]; 00182 00183 if (!fieldValues.ContainsKey(keyValue)) 00184 { 00185 fieldValues.Add(keyValue, (sqlReader.FieldCount > 1) ? sqlReader[1].ToString() : sqlReader[dynamicMaskData.DynamicMask.Table.PrimaryKeyFieldName].ToString()); 00186 } 00187 } 00188 con.Close(); 00189 } 00190 dynamicMaskData.SingleListFieldValues = fieldValues; 00191 } 00192 00193 public static void ApplyDataRoleRestrictions(string queryName, string parameter, string grouping, string restrictions, DataTable dt, RAIS.Common.UserManagement.UserData currentUser, int dynamicMaskID) 00194 { 00195 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00196 00197 string sqlString = "SELECT * FROM [" + queryName + "]" + parameter + " WHERE " + restrictions.Remove(0, 4); 00198 if (!String.IsNullOrEmpty(grouping)) 00199 sqlString = "SELECT [" + grouping + "] FROM [" + queryName + "]" + parameter + " WHERE " + restrictions.Remove(0, 4) + " GROUP BY [" + grouping + "]"; 00200 00201 dt.Reset(); 00202 SqlDataAdapter dAdapt = new SqlDataAdapter(sqlString, con); 00203 dAdapt.Fill(dt); 00204 } 00205 00206 public static DataTable GetDataRoleRestrictions(RAIS.Common.UserManagement.DataRole dataRole, ref string returnMessage) 00207 { 00208 try 00209 { 00210 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00211 SqlDataAdapter dAdapt = new SqlDataAdapter("SELECT [PK Data Role Additional Restriction ID], [Restriction Name], [Query Name], [Mask Name], [Subcategory Name], [Category Name]" + 00212 " FROM [Data Role Additional Restriction]" + 00213 " FULL OUTER JOIN Mask ON Mask.[PK Mask ID] = [Data Role Additional Restriction].[FK Mask ID]" + 00214 " FULL OUTER JOIN Subcategory ON Subcategory.[PK Subcategory ID] = [Data Role Additional Restriction].[FK Subcategory ID]" + 00215 " FULL OUTER JOIN Category ON Category.[PK Category ID] = [Data Role Additional Restriction].[FK Category ID]" + 00216 " WHERE [FK Data Role ID] = " + dataRole.ID + 00217 " ORDER BY [Restriction Name]", con); 00218 DataTable dtRestrictions = new DataTable(); 00219 dAdapt.Fill(dtRestrictions); 00220 return dtRestrictions; 00221 } 00222 catch (Exception ex) 00223 { 00224 returnMessage = ex.ToString(); 00225 return new DataTable(); 00226 } 00227 } 00228 00229 public static DataRow GetDataRoleRestriction(string restrictionID, ref string returnMessage) 00230 { 00231 try 00232 { 00233 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00234 SqlDataAdapter dAdapt = new SqlDataAdapter("SELECT [Restriction Name], [Query Name], [Mask Name], [Subcategory Name], [Category Name], [Selected Field]" + 00235 " FROM [Data Role Additional Restriction]" + 00236 " FULL OUTER JOIN Mask ON Mask.[PK Mask ID] = [Data Role Additional Restriction].[FK Mask ID]" + 00237 " FULL OUTER JOIN Subcategory ON Subcategory.[PK Subcategory ID] = [Data Role Additional Restriction].[FK Subcategory ID]" + 00238 " FULL OUTER JOIN Category ON Category.[PK Category ID] = [Data Role Additional Restriction].[FK Category ID]" + 00239 " WHERE [PK Data Role Additional Restriction ID] = " + restrictionID, con); 00240 DataTable dtRestrictions = new DataTable(); 00241 dAdapt.Fill(dtRestrictions); 00242 if (dtRestrictions.Rows.Count > 0) 00243 return dtRestrictions.Rows[0]; 00244 else return null; 00245 } 00246 catch (Exception ex) 00247 { 00248 returnMessage = ex.ToString(); 00249 return null; 00250 } 00251 } 00252 00253 public static bool AddDataRoleRestriction(string restrictionName, string queryName, string dataRoleID, string categoryID, string subcategoryID, string maskID, ref string returnMessage) 00254 { 00255 try 00256 { 00257 string sqlString = "SELECT * FROM [" + queryName + "]()"; 00258 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00259 SqlDataAdapter dAdapt = new SqlDataAdapter(sqlString, con); 00260 DataTable dt = new DataTable(); 00261 dAdapt.Fill(dt); 00262 string selectedField = dt.Columns[0].ToString(); 00263 00264 sqlString = "INSERT INTO [Data Role Additional Restriction]([Restriction Name], [Query Name], [Selected Field], [FK Data Role ID], [FK Category ID], [FK Subcategory ID], [FK Mask ID]) " + 00265 "VALUES('" + restrictionName + "', '" + queryName + "', '" + selectedField + "', " + dataRoleID + ", " + categoryID + ", " + subcategoryID + ", " + maskID + ")"; 00266 SqlCommand cmd = new SqlCommand(sqlString, con); 00267 con.Open(); 00268 int rowsAffected = cmd.ExecuteNonQuery(); 00269 con.Close(); 00270 if (rowsAffected == 1) 00271 return true; 00272 else 00273 { 00274 returnMessage = "Error occured on database level."; 00275 return false; 00276 } 00277 } 00278 catch (Exception ex) 00279 { 00280 returnMessage = ex.ToString(); 00281 return false; 00282 } 00283 } 00284 00285 public static bool EditDataRoleRestriction(string restrictionID, string restrictionName, string queryName, string dataRoleID, string categoryID, string subcategoryID, string maskID, ref string returnMessage) 00286 { 00287 try 00288 { 00289 string sqlString = "SELECT * FROM [" + queryName + "]()"; 00290 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00291 SqlDataAdapter dAdapt = new SqlDataAdapter(sqlString, con); 00292 DataTable dt = new DataTable(); 00293 dAdapt.Fill(dt); 00294 string selectedField = dt.Columns[0].ToString(); 00295 00296 sqlString = "UPDATE [Data Role Additional Restriction] SET [Restriction Name] = '" + restrictionName + "', " + 00297 "[Query Name] = '" + queryName + "', [Selected Field] = '" + selectedField + "', " + 00298 "[FK Data Role ID] = " + dataRoleID + ", [FK Category ID] = " + categoryID + ", " + 00299 "[FK Subcategory ID] = " + subcategoryID + ", [FK Mask ID] = " + maskID + " " + 00300 "WHERE [PK Data Role Additional Restriction ID] = " + restrictionID; 00301 00302 SqlCommand cmd = new SqlCommand(sqlString, con); 00303 con.Open(); 00304 int rowsAffected = cmd.ExecuteNonQuery(); 00305 con.Close(); 00306 if (rowsAffected == 1) 00307 return true; 00308 else 00309 { 00310 returnMessage = "Error occured on database level."; 00311 return false; 00312 } 00313 } 00314 catch (Exception ex) 00315 { 00316 returnMessage = ex.ToString(); 00317 return false; 00318 } 00319 } 00320 00321 public static bool DeleteDataRoleRestriction(string restrictionID, ref string returnMessage) 00322 { 00323 try 00324 { 00325 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00326 string sqlString = "DELETE FROM [Data Role Additional Restriction] " + 00327 "WHERE [PK Data Role Additional Restriction ID] = " + restrictionID; 00328 SqlCommand cmd = new SqlCommand(sqlString, con); 00329 con.Open(); 00330 int rowsAffected = cmd.ExecuteNonQuery(); 00331 con.Close(); 00332 if (rowsAffected == 1) 00333 return true; 00334 else 00335 { 00336 returnMessage = "Error occured on database level."; 00337 return false; 00338 } 00339 } 00340 catch (Exception ex) 00341 { 00342 returnMessage = ex.ToString(); 00343 return false; 00344 } 00345 } 00346 00347 public static bool CheckQuery(RAIS.Common.TableManagement.Query query, ref string returnMessage) 00348 { 00349 try 00350 { 00351 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00352 DataTable dt = new DataTable(); 00353 SqlDataAdapter dAdapt = new SqlDataAdapter(query.QueryText, con); 00354 dAdapt.Fill(dt); 00355 if (dt.Columns.Count == 1) 00356 { 00357 returnMessage = Common.Messages.QUERY_CREATED; 00358 return true; 00359 } 00360 else 00361 { 00362 returnMessage = "Only one field allowed in SELECT statement!"; 00363 return false; 00364 } 00365 } 00366 catch (Exception ex) 00367 { 00368 returnMessage = ex.ToString(); 00369 return false; 00370 } 00371 } 00372 00373 public static List<string> GetQueryAssignedRestrictions(string queryName) 00374 { 00375 List<string> result = new List<string>(); 00376 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00377 SqlCommand cmd = new SqlCommand("SELECT [Restriction Name] FROM [Data Role Additional Restriction] " + 00378 "WHERE [Query Name] = '" + queryName + "'", con); 00379 con.Open(); 00380 SqlDataReader reader = cmd.ExecuteReader(); 00381 while (reader.Read()) 00382 { 00383 result.Add(reader[0].ToString()); 00384 } 00385 con.Close(); 00386 return result; 00387 } 00388 00389 public static DataTable GetDynamicMasks(string categoryID, string subcategoryID, string maskID) 00390 { 00391 DataTable result = new DataTable(); 00392 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00393 string selString = GetDynamicMaskSelectString(categoryID, subcategoryID, maskID, false); 00394 SqlDataAdapter dAdapt = new SqlDataAdapter(selString, con); 00395 dAdapt.Fill(result); 00396 return result; 00397 } 00398 00399 public static DataTable GetCategorySubcategoryAndMaskNames(string categoryID, string subcategoryID, string maskID) 00400 { 00401 DataTable result = new DataTable(); 00402 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00403 string sqlString = "SELECT Category.[Category Name], Subcategory.[Subcategory Name], Mask.[Mask Name], Mask.[PK Mask ID] " + 00404 "FROM [Dynamic Mask] " + 00405 "FULL OUTER JOIN Mask ON [Dynamic Mask].[FK Mask ID] = Mask.[PK Mask ID] " + 00406 "FULL OUTER JOIN Subcategory ON Mask.[FK Subcategory ID] = Subcategory.[PK Subcategory ID] " + 00407 "FULL OUTER JOIN Category ON Category.[PK Category ID] = Subcategory.[FK Category ID] " + 00408 "WHERE [Dynamic Mask].[PK Dynamic Mask ID] IN (" + GetDynamicMaskSelectString(categoryID, subcategoryID, maskID, true) + ")"; 00409 SqlDataAdapter dAdapt = new SqlDataAdapter(sqlString, con); 00410 dAdapt.Fill(result); 00411 result.Columns[0].ColumnName = "MainMenu"; 00412 result.Columns[1].ColumnName = "Submenu"; 00413 result.Columns[2].ColumnName = "MaskName"; 00414 result.Columns[3].ColumnName = "MaskID"; 00415 return result; 00416 } 00417 00418 #endregion 00419 00420 #region PRIVATE HELPER METHODS 00421 00422 static bool IsDynamicMaskRestricted(DataRow restrictionRow, DataRow currentMaskDataRow) 00423 { 00424 if (currentMaskDataRow != null) 00425 { 00426 string maskID = currentMaskDataRow["PK Mask ID"].ToString(); 00427 string subcategoryID = currentMaskDataRow["PK Subcategory ID"].ToString(); 00428 string categoryID = currentMaskDataRow["FK Category ID"].ToString(); 00429 00430 if (restrictionRow["FK Mask ID"].ToString() == maskID) 00431 return true; 00432 else if (String.IsNullOrEmpty(restrictionRow["FK Mask ID"].ToString()) && restrictionRow["FK Subcategory ID"].ToString() == subcategoryID) 00433 return true; 00434 else if (String.IsNullOrEmpty(restrictionRow["FK Mask ID"].ToString()) && String.IsNullOrEmpty(restrictionRow["FK Subcategory ID"].ToString()) && restrictionRow["FK Category ID"].ToString() == categoryID) 00435 return true; 00436 else if (String.IsNullOrEmpty(restrictionRow["FK Mask ID"].ToString()) && String.IsNullOrEmpty(restrictionRow["FK Subcategory ID"].ToString()) && String.IsNullOrEmpty(restrictionRow["FK Category ID"].ToString())) 00437 return true; 00438 } 00439 return false; 00440 } 00441 00442 static DataRow GetCategorySubcategoryAndMaskID(int dynamicMaskID) 00443 { 00444 string sqlString = "SELECT Mask.[PK Mask ID], Subcategory.[PK Subcategory ID], Subcategory.[FK Category ID] " + 00445 "FROM Mask INNER JOIN [Dynamic Mask] ON [Dynamic Mask].[FK Mask ID] = Mask.[PK Mask ID] " + 00446 "INNER JOIN Subcategory ON Mask.[FK Subcategory ID] = Subcategory.[PK Subcategory ID] " + 00447 "WHERE [Dynamic Mask].[PK Dynamic Mask ID] = " + dynamicMaskID; 00448 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00449 DataTable dt = new DataTable(); 00450 SqlDataAdapter dAdapt = new SqlDataAdapter(sqlString, con); 00451 dAdapt.Fill(dt); 00452 if (dt.Rows.Count > 0) 00453 return dt.Rows[0]; 00454 else 00455 return null; 00456 } 00457 00458 static string GetSqlString(DynamicMaskData dynamicMaskData, ref string primaryKey, ref string colNames, bool applyGrouping) 00459 { 00460 string sqlString = String.Empty; 00461 using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString)) 00462 { 00463 string queryString = String.Empty; 00464 if (dynamicMaskData.DynamicMask.Order == 0) 00465 queryString = GetQueryString(dynamicMaskData); 00466 else 00467 { 00468 SqlCommand cmd = new SqlCommand("SELECT [FK Mask ID] FROM [Dynamic Mask] WHERE [PK Dynamic Mask ID] = " + dynamicMaskData.DynamicMasks[0].ID, con); 00469 con.Open(); 00470 SqlDataReader reader = cmd.ExecuteReader(); 00471 reader.Read(); 00472 int maskID = (int)reader[0]; 00473 con.Close(); 00474 queryString = GetQueryString(DynamicMaskManagement.GetDynamicMaskData(maskID, 0, 0, dynamicMaskData.CurrentUser, false)); 00475 } 00476 SqlDataAdapter dAdapt = new SqlDataAdapter("SELECT * FROM " + queryString, con); 00477 DataTable dt = new DataTable(); 00478 dAdapt.Fill(dt); 00479 sqlString = "SELECT "; 00480 colNames = GetQueryColumnNames(dt); 00481 if (applyGrouping) 00482 sqlString += colNames; 00483 else 00484 sqlString += "*"; 00485 sqlString += " FROM " + queryString + " AS tableAlias"; 00486 System.Collections.Specialized.OrderedDictionary multiLookupJoins = new System.Collections.Specialized.OrderedDictionary(); 00487 bool firstJoin = true; 00488 foreach (DynamicMask dynamicMask in dynamicMaskData.DynamicMasks.Values) 00489 { 00490 if (dynamicMask.Order != 0) 00491 { 00492 string tableName = dynamicMask.Table.Name; 00493 if (firstJoin && !dynamicMask.Table.PrimaryKeyFieldName.Equals(dynamicMaskData.DynamicMasks[0].Table.PrimaryKeyFieldName)) 00494 sqlString += " FULL OUTER JOIN [" + tableName + "] ON [" + tableName + "].[" + dynamicMask.Table.PrimaryKeyFieldName + "] = tableAlias.[" + dynamicMask.Table.PrimaryKeyFieldName + "]"; 00495 else if (!dynamicMask.Table.PrimaryKeyFieldName.Equals(dynamicMaskData.DynamicMasks[0].Table.PrimaryKeyFieldName)) 00496 sqlString += " FULL OUTER JOIN [" + tableName + "] ON [" + tableName + "].[" + dynamicMaskData.DynamicMasks[0].Table.ForeignKeyFieldName + "] = tableAlias.[" + dynamicMaskData.DynamicMasks[0].Table.PrimaryKeyFieldName + "]"; 00497 else 00498 sqlString += " FULL OUTER JOIN [" + tableName + "] ON [" + tableName + "].[" + dynamicMaskData.DynamicMasks[0].Table.PrimaryKeyFieldName + "] = tableAlias.[" + dynamicMaskData.DynamicMasks[0].Table.PrimaryKeyFieldName + "]"; 00499 firstJoin = false; 00500 #region Multilookups 00501 if (dynamicMask.Table.Fields != null) 00502 { 00503 foreach (Field field in dynamicMask.Table.Fields.Values) 00504 { 00505 Table table = dynamicMask.Table; 00506 if (field.Type == Field.FieldType.MultipleLookup) 00507 { 00508 if (!multiLookupJoins.Contains(table.Name + " " + field.RelatedTable.Name)) 00509 multiLookupJoins.Add(table.Name + " " + field.RelatedTable.Name, " LEFT OUTER JOIN [" + table.Name + " " + field.RelatedTable.Name + "] ON [" + table.Name + " " + field.RelatedTable.Name + "].[" + table.ForeignKeyFieldName + "] = [" + table.Name + "].[" + table.PrimaryKeyFieldName + "]"); 00510 else 00511 { 00512 // Join will be removed first and added again because the order of the joins in the list is important 00513 string temp = multiLookupJoins[table.Name + " " + field.RelatedTable.Name].ToString(); 00514 multiLookupJoins.Remove(table.Name + " " + field.RelatedTable.Name); 00515 multiLookupJoins.Add(table.Name + " " + field.RelatedTable.Name, temp + " AND [" + table.Name + " " + field.RelatedTable.Name + "].[" + table.ForeignKeyFieldName + "] = [" + table.Name + "].[" + table.PrimaryKeyFieldName + "]"); 00516 } 00517 if (!multiLookupJoins.Contains(field.RelatedTable.Name)) 00518 multiLookupJoins.Add(field.RelatedTable.Name, " LEFT OUTER JOIN [" + field.RelatedTable.Name + "] ON [" + field.RelatedTable.Name + "].[" + field.RelatedTable.PrimaryKeyFieldName + "] = [" + table.Name + " " + field.RelatedTable.Name + "].[" + field.RelatedTable.ForeignKeyFieldName + "]"); 00519 else 00520 { 00521 // Join will be removed first and added again because the order of the joins in the list is important 00522 string temp = multiLookupJoins[field.RelatedTable.Name].ToString(); 00523 multiLookupJoins.Remove(field.RelatedTable.Name); 00524 multiLookupJoins.Add(field.RelatedTable.Name, temp + " AND [" + field.RelatedTable.Name + "].[" + field.RelatedTable.PrimaryKeyFieldName + "] = [" + table.Name + " " + field.RelatedTable.Name + "].[" + field.RelatedTable.ForeignKeyFieldName + "]"); 00525 } 00526 } 00527 } 00528 } 00529 #endregion 00530 } 00531 } 00532 for (int i = 0; i < multiLookupJoins.Count; i++) 00533 { 00534 string multiLookupJoin = multiLookupJoins[i].ToString(); 00535 sqlString += multiLookupJoin; 00536 } 00537 primaryKey = dt.Columns[0].ToString(); 00538 } 00539 return sqlString; 00540 } 00541 00542 static string GetSqlString(DynamicMaskData dynamicMaskData) 00543 { 00544 string primaryKey = String.Empty; 00545 string colNames = String.Empty; 00546 return GetSqlString(dynamicMaskData, ref primaryKey, ref colNames, false); 00547 } 00548 00549 static string GetSqlString(DynamicMaskData dynamicMaskData, int key, string whereFieldName, int whereFieldValue) 00550 { 00551 string sqlString = String.Empty; 00552 using (SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString)) 00553 { 00554 string queryString = String.Empty; 00555 string colNames = String.Empty; 00556 00557 if (dynamicMaskData.DynamicMask.Order == 0) 00558 { 00559 queryString = GetQueryString(dynamicMaskData); 00560 SqlDataAdapter dAdapt = new SqlDataAdapter("SELECT * FROM " + queryString, con); 00561 DataTable dt = new DataTable(); 00562 dAdapt.Fill(dt); 00563 colNames = GetQueryColumnNames(dt); 00564 } 00565 else 00566 { 00567 SqlCommand cmd = new SqlCommand("SELECT [FK Mask ID] FROM [Dynamic Mask] WHERE [PK Dynamic Mask ID] = " + dynamicMaskData.DynamicMasks[0].ID, con); 00568 con.Open(); 00569 SqlDataReader reader = cmd.ExecuteReader(); 00570 reader.Read(); 00571 int maskID = (int)reader[0]; 00572 con.Close(); 00573 queryString = GetQueryString(DynamicMaskManagement.GetDynamicMaskData(maskID, 0, 0, dynamicMaskData.CurrentUser, false)); 00574 00575 #region SELECT STATEMENT 00576 Table table = dynamicMaskData.DynamicMask.Table; 00577 string queryName = dynamicMaskData.DynamicMask.QueryName; 00578 00579 bool selectFromTable = ((queryName == string.Empty) && (table.Name == table.QueryName)); 00580 queryName = (queryName == string.Empty) ? table.QueryName : queryName; 00581 queryName = "[" + queryName; 00582 if (queryName.Contains("(")) 00583 queryName = queryName.Replace("(", "]("); 00584 else 00585 queryName = queryName + "]"; 00586 00587 con.Open(); 00588 if (selectFromTable) 00589 { 00590 string fieldKey = ""; 00591 foreach (var field in table.Fields) 00592 { 00593 if (field.Value.Order == 1) 00594 { 00595 fieldKey = field.Key; 00596 break; 00597 } 00598 } 00599 if (fieldKey == "") 00600 { 00601 if (table.VisibleTextFieldName != "" && table.PrimaryKeyFieldName != table.VisibleTextFieldName) 00602 colNames = string.Format("[{0}], [{1}]", table.PrimaryKeyFieldName, table.VisibleTextFieldName); 00603 else 00604 colNames = string.Format("*", table.PrimaryKeyFieldName); 00605 } 00606 else 00607 { 00608 colNames = string.Format("[" + dynamicMaskData.DynamicMask.Table.Name + "].[{0}], [" + dynamicMaskData.DynamicMask.Table.Name + "].[{1}]", table.PrimaryKeyFieldName, table.VisibleTextFieldName); 00609 } 00610 } 00611 else 00612 colNames = "*"; 00613 00614 #endregion 00615 } 00616 00617 sqlString = "SELECT " + colNames + " FROM " + queryString + " AS tableAlias"; 00618 System.Collections.Specialized.OrderedDictionary multiLookupJoins = new System.Collections.Specialized.OrderedDictionary(); 00619 bool firstJoin = true; 00620 foreach (DynamicMask dynamicMask in dynamicMaskData.DynamicMasks.Values) 00621 { 00622 if (dynamicMask.Order != 0) 00623 { 00624 string tableName = dynamicMask.Table.Name; 00625 if (firstJoin && !dynamicMask.Table.PrimaryKeyFieldName.Equals(dynamicMaskData.DynamicMasks[0].Table.PrimaryKeyFieldName)) 00626 sqlString += " FULL OUTER JOIN [" + tableName + "] ON [" + tableName + "].[" + dynamicMask.Table.PrimaryKeyFieldName + "] = tableAlias.[" + dynamicMask.Table.PrimaryKeyFieldName + "]"; 00627 else if (!dynamicMask.Table.PrimaryKeyFieldName.Equals(dynamicMaskData.DynamicMasks[0].Table.PrimaryKeyFieldName)) 00628 sqlString += " FULL OUTER JOIN [" + tableName + "] ON [" + tableName + "].[" + dynamicMaskData.DynamicMasks[0].Table.ForeignKeyFieldName + "] = tableAlias.[" + dynamicMaskData.DynamicMasks[0].Table.PrimaryKeyFieldName + "]"; 00629 else 00630 sqlString += " FULL OUTER JOIN [" + tableName + "] ON [" + tableName + "].[" + dynamicMaskData.DynamicMasks[0].Table.PrimaryKeyFieldName + "] = tableAlias.[" + dynamicMaskData.DynamicMasks[0].Table.PrimaryKeyFieldName + "]"; 00631 firstJoin = false; 00632 #region Multilookups 00633 if (dynamicMask.Table.Fields != null) 00634 { 00635 foreach (Field field in dynamicMask.Table.Fields.Values) 00636 { 00637 Table table = dynamicMask.Table; 00638 if (field.Type == Field.FieldType.MultipleLookup) 00639 { 00640 if (!multiLookupJoins.Contains(table.Name + " " + field.RelatedTable.Name)) 00641 multiLookupJoins.Add(table.Name + " " + field.RelatedTable.Name, " LEFT OUTER JOIN [" + table.Name + " " + field.RelatedTable.Name + "] ON [" + table.Name + " " + field.RelatedTable.Name + "].[" + table.ForeignKeyFieldName + "] = [" + table.Name + "].[" + table.PrimaryKeyFieldName + "]"); 00642 else 00643 { 00644 // Join will be removed first and added again because the order of the joins in the list is important 00645 string temp = multiLookupJoins[table.Name + " " + field.RelatedTable.Name].ToString(); 00646 multiLookupJoins.Remove(table.Name + " " + field.RelatedTable.Name); 00647 multiLookupJoins.Add(table.Name + " " + field.RelatedTable.Name, temp + " AND [" + table.Name + " " + field.RelatedTable.Name + "].[" + table.ForeignKeyFieldName + "] = [" + table.Name + "].[" + table.PrimaryKeyFieldName + "]"); 00648 } 00649 if (!multiLookupJoins.Contains(field.RelatedTable.Name)) 00650 multiLookupJoins.Add(field.RelatedTable.Name, " LEFT OUTER JOIN [" + field.RelatedTable.Name + "] ON [" + field.RelatedTable.Name + "].[" + field.RelatedTable.PrimaryKeyFieldName + "] = [" + table.Name + " " + field.RelatedTable.Name + "].[" + field.RelatedTable.ForeignKeyFieldName + "]"); 00651 else 00652 { 00653 // Join will be removed first and added again because the order of the joins in the list is important 00654 string temp = multiLookupJoins[field.RelatedTable.Name].ToString(); 00655 multiLookupJoins.Remove(field.RelatedTable.Name); 00656 multiLookupJoins.Add(field.RelatedTable.Name, temp + " AND [" + field.RelatedTable.Name + "].[" + field.RelatedTable.PrimaryKeyFieldName + "] = [" + table.Name + " " + field.RelatedTable.Name + "].[" + field.RelatedTable.ForeignKeyFieldName + "]"); 00657 } 00658 } 00659 } 00660 } 00661 #endregion 00662 } 00663 } 00664 for (int i = 0; i < multiLookupJoins.Count; i++) 00665 { 00666 string multiLookupJoin = multiLookupJoins[i].ToString(); 00667 sqlString += multiLookupJoin; 00668 } 00669 00670 //sqlString += " WHERE tableAlias.[" + primaryKey + "] IS NOT NULL"; 00671 if (key != 0) 00672 sqlString += string.Format(" WHERE [{2}].[{0}] = {1}", dynamicMaskData.DynamicMask.Table.PrimaryKeyFieldName, key, dynamicMaskData.DynamicMask.Table.Name) + GetRestrictions(dynamicMaskData); 00673 else if (whereFieldValue != 0) 00674 sqlString += string.Format(" WHERE [{2}].[{0}] = {1}", whereFieldName, whereFieldValue, dynamicMaskData.DynamicMask.Table.Name) + GetRestrictions(dynamicMaskData); 00675 else 00676 sqlString += " WHERE " + GetRestrictions(dynamicMaskData).Remove(0, 4); 00677 00678 if (!colNames.Equals("*")) 00679 sqlString += " GROUP BY " + colNames; 00680 00681 if (dynamicMaskData.DynamicMask.Table.VisibleTextFieldName != "" && ((dynamicMaskData.DynamicMask.QueryName == string.Empty) && (dynamicMaskData.DynamicMask.Table.Name == dynamicMaskData.DynamicMask.Table.QueryName))) 00682 { 00683 string tableName = "tableAlias"; 00684 if (dynamicMaskData.DynamicMask.Order != 0) 00685 tableName = "[" + dynamicMaskData.DynamicMask.Table.Name + "]"; 00686 sqlString += string.Format(" ORDER BY {1}.[{0}]", dynamicMaskData.DynamicMask.Table.VisibleTextFieldName, tableName); 00687 if (dynamicMaskData.DynamicMask.Table.Fields.ContainsKey(dynamicMaskData.DynamicMask.Table.VisibleTextFieldName) && dynamicMaskData.DynamicMask.Table.Fields[dynamicMaskData.DynamicMask.Table.VisibleTextFieldName].Type == Field.FieldType.Date) 00688 { 00689 sqlString += " DESC"; 00690 } 00691 } 00692 } 00693 return sqlString; 00694 } 00695 00696 static string GetQueryString(DynamicMaskData dynamicMaskData) 00697 { 00698 string sqlString = string.Format("[{0}]" 00699 , (dynamicMaskData.DynamicMask.QueryName == "") 00700 ? dynamicMaskData.DynamicMask.Table.Name 00701 : dynamicMaskData.DynamicMask.QueryName); 00702 if (dynamicMaskData.DynamicMask.QueryName != "") 00703 { 00704 SortedDictionary<int, DynamicMaskFilterData> queryOrderedFiltersData = new SortedDictionary<int, DynamicMaskFilterData>(); 00705 foreach (KeyValuePair<int, DynamicMaskFilterData> kvp in dynamicMaskData.FiltersData) 00706 { 00707 DynamicMaskFilterData filterData = kvp.Value; 00708 if (filterData.DynamicMaskFilter.UseInQuery) 00709 queryOrderedFiltersData.Add(filterData.DynamicMaskFilter.OrderInQuery, filterData); 00710 } 00711 00712 sqlString += "("; 00713 bool isFirstParameter = true; 00714 foreach (KeyValuePair<int, DynamicMaskFilterData> kvp in queryOrderedFiltersData) 00715 { 00716 DynamicMaskFilterData filterData = kvp.Value; 00717 if (filterData.DynamicMaskFilter.UseInQuery) 00718 { 00719 sqlString += (isFirstParameter) ? "" : ", "; 00720 if (filterData.DynamicMaskFilter.Name == Common.Constants.FACILITY_PARAMETER_NAME 00721 && !filterData.DynamicMaskFilter.VisibleOnForm) 00722 { 00723 sqlString += (dynamicMaskData.SelectedFacilityID == 0) 00724 ? "null" 00725 : dynamicMaskData.SelectedFacilityID.ToString(); 00726 } 00727 else 00728 if (filterData.DynamicMaskFilter.Name == Common.Constants.DEPARTMENT_PARAMETER_NAME 00729 && !filterData.DynamicMaskFilter.VisibleOnForm) 00730 { 00731 sqlString += (dynamicMaskData.SelectedDepartmentID == 0) 00732 ? "null" 00733 : dynamicMaskData.SelectedDepartmentID.ToString(); 00734 } 00735 else 00736 if (filterData.DynamicMaskFilter.Name == Common.Constants.USER_PARAMETER_NAME) 00737 { 00738 sqlString += (dynamicMaskData.CurrentUser.ID == 0) 00739 ? "null" 00740 : dynamicMaskData.CurrentUser.ID.ToString(); 00741 } 00742 else 00743 { 00744 if (filterData.DynamicMaskFilter.Type == DynamicMaskFilter.FilterType.DatesRage) 00745 { 00746 // Set end of the day 00747 DateTime dateUntil = filterData.DateUntil.AddSeconds(86399); 00748 00751 //sqlString += string.Format("'{0}-{1}-{2} {3}', '{4}-{5}-{6} {7}'" 00752 // , filterData.DateFrom.Year 00753 // , filterData.DateFrom.Month 00754 // , filterData.DateFrom.Day 00755 // , filterData.DateFrom.TimeOfDay 00756 // , dateUntil.Year 00757 // , dateUntil.Month 00758 // , dateUntil.Day 00759 // , dateUntil.TimeOfDay); 00760 00761 //use canonical date format (= 120) for SQL-Servers in different languages 00762 sqlString += string.Format("CONVERT(datetime,'{0}-{1}-{2} {3}',120),CONVERT(datetime,'{4}-{5}-{6} {7}',120)" 00763 , filterData.DateFrom.Year 00764 , filterData.DateFrom.Month 00765 , filterData.DateFrom.Day 00766 , filterData.DateFrom.TimeOfDay 00767 , dateUntil.Year 00768 , dateUntil.Month 00769 , dateUntil.Day 00770 , dateUntil.TimeOfDay); 00771 00772 } 00773 else 00774 { 00775 sqlString += (filterData.CurrentLookupValue == 0) 00776 ? "null" 00777 : filterData.CurrentLookupValue.ToString(); 00778 } 00779 } 00780 isFirstParameter = false; 00781 } 00782 } 00783 sqlString += ")"; 00784 } 00785 return sqlString; 00786 } 00787 00788 static string GetQueryColumnNames(DataTable dt) 00789 { 00790 string colNames = String.Empty; 00791 foreach (DataColumn col in dt.Columns) 00792 { 00793 colNames += "tableAlias.[" + col.ColumnName + "], "; 00794 } 00795 colNames = colNames.Remove(colNames.Length - 2); 00796 return colNames; 00797 } 00798 00799 static string GetRestrictions(DynamicMaskData dynamicMaskData) 00800 { 00801 string restrictions = ""; 00802 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00803 SqlDataAdapter dAdapt = new SqlDataAdapter("SELECT * FROM [Data Role Additional Restriction] WHERE [FK Data Role ID] = " + dynamicMaskData.CurrentUser.DataRole.ID, con); 00804 DataTable dt = new DataTable(); 00805 dAdapt.Fill(dt); 00806 DataColumnCollection columns = GetAllDynamicMaskColumns(dynamicMaskData); 00807 DataRow currentMaskDataRow = GetCategorySubcategoryAndMaskID(dynamicMaskData.DynamicMask.ID); 00808 foreach (DataRow row in dt.Rows) 00809 { 00810 string selectedField = row["Selected Field"].ToString(); 00811 if (IsDynamicMaskRestricted(row, currentMaskDataRow)) 00812 { 00813 string tableAndField = GetTableNameWithFieldName(dynamicMaskData, selectedField); 00814 if (tableAndField != null) 00815 restrictions += " AND " + tableAndField + " IN (SELECT * FROM [" + row["Query Name"] + "]())"; 00816 } 00817 } 00818 return restrictions; 00819 } 00820 00821 static DataColumnCollection GetAllDynamicMaskColumns(DynamicMaskData dynamicMaskData) 00822 { 00823 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00824 string cmd = GetSqlString(dynamicMaskData); 00825 SqlDataAdapter dAdapt = new SqlDataAdapter(cmd, con); 00826 DataTable dt = new DataTable(); 00827 dAdapt.Fill(dt); 00828 return dt.Columns; 00829 } 00830 00831 static string GetTableNameWithFieldName(DynamicMaskData dynamicMaskData, string selectedField) 00832 { 00833 foreach (DynamicMask dynamicMask in dynamicMaskData.DynamicMasks.Values) 00834 { 00835 if (dynamicMaskData.DynamicMask.Order != 0) 00836 { 00837 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00838 SqlCommand cmd = new SqlCommand("SELECT [FK Mask ID] FROM [Dynamic Mask] WHERE [PK Dynamic Mask ID] = " + dynamicMaskData.DynamicMasks[0].ID, con); 00839 con.Open(); 00840 SqlDataReader reader = cmd.ExecuteReader(); 00841 reader.Read(); 00842 int maskID = (int)reader[0]; 00843 con.Close(); 00844 dynamicMaskData = DynamicMaskManagement.GetDynamicMaskData(maskID, 0, 0, dynamicMaskData.CurrentUser, false); 00845 } 00846 string result = CheckTable(dynamicMask.Table != null ? dynamicMask.Table.Name : dynamicMask.QueryName, selectedField, (dynamicMask.ID == dynamicMaskData.DynamicMasks[0].ID && !String.IsNullOrEmpty(dynamicMask.QueryName)) ? GetQueryString(dynamicMaskData) : null, dynamicMask.Table != null ? dynamicMask.Table.Fields : null, (dynamicMask.Order == 0)); 00847 if (!String.IsNullOrEmpty(result)) 00848 return result; 00849 } 00850 return null; 00851 } 00852 00853 static string CheckTable(string tableName, string selectedField, string queryString, Dictionary<string, Field> tableFields, bool isFirstTable) 00854 { 00855 bool isRecursiveCall = (tableFields == null); 00856 string selStr = "SELECT * FROM [" + tableName + "]"; 00857 bool hasQuery = !String.IsNullOrEmpty(queryString); 00858 if (hasQuery) 00859 selStr = "SELECT * FROM " + queryString; 00860 SqlConnection con = new SqlConnection(DataAccessUtilities.ConnectionString); 00861 SqlDataAdapter dAdapt = new SqlDataAdapter(selStr, con); 00862 DataTable dt = new DataTable(); 00863 dAdapt.Fill(dt); 00864 if (dt.Columns.Contains(selectedField)) 00865 { 00866 if (hasQuery || isFirstTable) 00867 return "tableAlias.[" + selectedField + "]"; 00868 else return "[" + tableName + "].[" + selectedField + "]"; 00869 } 00870 else if (dt.Columns.Contains("FK " + selectedField.Remove(0, 3))) 00871 { 00872 if (hasQuery || isFirstTable) 00873 return "tableAlias.[FK " + selectedField.Remove(0, 3) + "]"; 00874 else return "[" + tableName + "].[FK " + selectedField.Remove(0, 3) + "]"; 00875 } 00876 else if (dt.Columns.Contains("PK " + selectedField.Remove(0, 3))) 00877 { 00878 if (hasQuery || isFirstTable) 00879 return "tableAlias.[PK " + selectedField.Remove(0, 3) + "]"; 00880 else return "[" + tableName + "].[PK " + selectedField.Remove(0, 3) + "]"; 00881 } 00882 else if (!isRecursiveCall && tableFields != null) // Check Multi-Lookup Fields 00883 { 00884 foreach (Field field in tableFields.Values) 00885 { 00886 if (field.Type == Field.FieldType.MultipleLookup) 00887 { 00888 string result = CheckTable(field.RelatedTable.Name, selectedField, null, null, false); 00889 if (!String.IsNullOrEmpty(result)) 00890 return result; 00891 else 00892 { 00893 result = CheckTable((tableName + " " + field.RelatedTable.Name), selectedField, null, null, false); 00894 if (!String.IsNullOrEmpty(result)) 00895 return result; 00896 } 00897 } 00898 } 00899 } 00900 return null; 00901 } 00902 00903 static string GetDynamicMaskSelectString(string categoryID, string subcategoryID, string maskID, bool selectIdOnly) 00904 { 00905 string selectStatement = "*"; 00906 if (selectIdOnly) 00907 selectStatement = "[PK Dynamic Mask ID]"; 00908 if (String.IsNullOrEmpty(categoryID)) 00909 { 00910 // Get all dynamic masks of the system 00911 return "SELECT " + selectStatement + " FROM [Dynamic Mask] WHERE [Order] = 0"; 00912 } 00913 else if (String.IsNullOrEmpty(subcategoryID)) 00914 { 00915 // Get masks of certain category 00916 return "SELECT " + selectStatement + " FROM [Dynamic Mask] " + 00917 "INNER JOIN Mask ON Mask.[PK Mask ID] = [Dynamic Mask].[FK Mask ID] " + 00918 "INNER JOIN Subcategory ON Subcategory.[PK Subcategory ID] = Mask.[FK Subcategory ID]" + 00919 "WHERE [Dynamic Mask].[Order] = 0 " + 00920 "AND Subcategory.[FK Category ID] = " + categoryID; 00921 } 00922 else if (String.IsNullOrEmpty(maskID)) 00923 { 00924 // Get masks of certain subcategory 00925 return "SELECT " + selectStatement + " FROM [Dynamic Mask] " + 00926 "INNER JOIN Mask ON Mask.[PK Mask ID] = [Dynamic Mask].[FK Mask ID] " + 00927 "WHERE [Dynamic Mask].[Order] = 0 " + 00928 "AND Mask.[FK Subcategory ID] = " + subcategoryID; 00929 } 00930 else 00931 { 00932 // Get a single mask 00933 return "SELECT " + selectStatement + " FROM [Dynamic Mask] WHERE [Order] = 0 AND [FK Mask ID] = " + maskID; 00934 } 00935 } 00936 00937 #endregion 00938 } 00939 }
1.7.5 
